/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package model;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.LinkedList;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

/**
 *
 * @author SONY
 */
public class ProductDAO {
    
    DataSource ds;
    
    public ProductDAO() {
        try {
            Context context = new InitialContext();
            ds = (DataSource) context.lookup("java:comp/env/jdbc/FashionShopDatasource");
        } catch (Exception e) {
            System.err.println(e);
        }
    }
    
    public List getAllProducts() {
        List products = new LinkedList();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM Product";
            Statement stmt = conn.createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            while(rs.next()) {
                String pid = rs.getString("ProductID");
                String pname = rs.getNString("ProductName");
                int qoh = rs.getInt("QOH");
                double unitprice = rs.getDouble("UnitPrice");
                String imagename = rs.getNString("ImageName");
                String categoryid = rs.getString("CategoryID");
                Product product = new Product(pid, pname, qoh, unitprice, imagename, categoryid);
                products.add(product);
            }
        } catch(Exception e) {
            System.err.println(e);
        }
        return products;
    }
    
    public int getQOH(String pid) {
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM Product WHERE ProductID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                int qoh = rs.getInt("QOH");
                return qoh;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return -1;
    }
    
    public Product search_byProductID(String pid) {
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM Product WHERE ProductID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, pid);
            ResultSet rs = pstmt.executeQuery();
            if (rs.next()) {
                String pname = rs.getNString("ProductName");
                int qoh = rs.getInt("QOH");
                double unitprice = rs.getDouble("UnitPrice");
                String imagename = rs.getNString("ImageName");
                String categoryid = rs.getString("CategoryID");
                Product product = new Product(pid, pname, qoh, unitprice, imagename, categoryid);
                return product;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return null;
    }
    
    public ArrayList<Product> getProducts_byKeyword(String keyword) {
        ArrayList<Product> products = new ArrayList<Product>();
        try {
            Connection conn = ds.getConnection();
            String sql = "SELECT * FROM Product WHERE ProductName LIKE ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            String where_value = "%" + keyword + "%";
            pstmt.setString(1, where_value);
            ResultSet rs = pstmt.executeQuery();
            while(rs.next()) {
                String pid = rs.getString("ProductID");
                String pname = rs.getNString("ProductName");
                int qoh = rs.getInt("QOH");
                double unitprice = rs.getDouble("UnitPrice");
                String imagename = rs.getNString("ImageName");
                String categoryid = rs.getString("CategoryID");
                Product product = new Product(pid, pname, qoh, unitprice, imagename, categoryid);
                products.add(product);
            }
        } catch(Exception e) {
            System.err.println(e);
        }
        return products;
    }
    
    public boolean update_QOH (String ProductID, int new_QOH) {
        try {
            Connection conn = ds.getConnection();
            String sql = "UPDATE Product SET QOH=? WHERE ProductID=?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setInt(1, new_QOH);
            pstmt.setNString(2, ProductID);
            if (pstmt.executeUpdate() > 0) {
                return true;
            }
        } catch (Exception e) {
            System.err.println(e);
        }
        return false;
    }    
}
